/* $RCSFile: MagicUrlsDB.cs $
 * Apache license v2 2011 by Christian Surieux
 * $Source: ?/ADNCRM.Core/DAL/MagicUrlsDB.cs $
 * $log$
 * Revision 19 2010/08/16 22:52:09  christian.surieux
 *   Suppress telerik version from web.config, Add comments for connection string, Add dbo. to adn_version table creation source in DBDataStore
 * Revision 6 2010/08/06 19:56:28  christian.surieux
 *   Added Log to All cs files
 */

using System;
using System.Diagnostics;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

using AddonNice.Diagnostics;
using AddonNice.Configuration;


namespace AddonNice.Settings
{

	/// <summary>
	/// Manage Magic Urls Data in DB to avoid web garden pbs with xml file
	/// </summary>
	public class MagicUrlsDB : DBDataStore
	{

		/// <summary>
		/// The AddMagicUrls function is used to ADD MagicUrls to the DatabaseDB
		/// </summary>
		public int AddMagicUrls(int PortalID,string MagicKey,string MagicValue,string MagicComment,int MagicScheduleDay,int MagicScheduleHours) 
		{
			SqlCommand myCommand			= new SqlCommand("dbo.adn_AddMagicUrls", myConnection);
            myCommand.CommandType			= CommandType.StoredProcedure;

            //Output parm
            SqlParameter parameterItemID	= new SqlParameter(StStr.ATItemID, SqlDbType.Int, 4);
            parameterItemID.Direction		= ParameterDirection.Output;
            myCommand.Parameters.Add(parameterItemID);
			
            AddPortalIDParameters(myCommand,PortalID);
			
            SqlParameter parameterMagicKey	= new SqlParameter("@MagicKey", SqlDbType.NVarChar, 128);
            parameterMagicKey.Value			= (MagicKey.Length > 128) ? MagicKey.Substring(0,128):MagicKey;
            myCommand.Parameters.Add(parameterMagicKey);

            SqlParameter parameterMagicValue	= new SqlParameter("@MagicValue", SqlDbType.NVarChar, 512);
            parameterMagicValue.Value			= (MagicValue.Length > 512) ? MagicValue.Substring(0,512):MagicValue;
            myCommand.Parameters.Add(parameterMagicValue);

			SqlParameter parameterMagicComment	= new SqlParameter("@MagicComment", SqlDbType.NVarChar, 512);
            parameterMagicComment.Value			= (MagicComment.Length > 512) ? MagicComment.Substring(0,512):MagicComment;
            myCommand.Parameters.Add(parameterMagicComment);

			SqlParameter parameterMagicScheduleDay	= new SqlParameter("@MagicScheduleDay", SqlDbType.Int, 4);
            parameterMagicScheduleDay.Value			= MagicScheduleDay;
            myCommand.Parameters.Add(parameterMagicScheduleDay);

			SqlParameter parameterMagicScheduleHours	= new SqlParameter("@MagicScheduleHours", SqlDbType.Int, 4);
            parameterMagicScheduleHours.Value			= MagicScheduleHours;
            myCommand.Parameters.Add(parameterMagicScheduleHours);

			int ret							=	-1;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				ret			=	Convert.ToInt32(parameterItemID.Value);
			}
			catch(Exception exp)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]MagicUrlsDB AddMagicUrls  MagicKey:{0} ex : {1}",MagicKey, exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}

            return ret;
        }


		/// <summary>
		/// The GetMagicAllUrls function is used to get all the MagicUrls 		
		/// </summary>
		public DataTable GetAllMagicUrls() 
		{
			SqlDataAdapter myCommand			= new SqlDataAdapter("dbo.adn_GetAllMagicUrls", myConnection);
            myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable myDataTb					= new DataTable();
			try
			{
				myCommand.Fill(myDataTb);
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]MagicUrlsDB GetMagicUrls  ex : {0}",exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}

            //  return the DataTable
			return CheckDataTableForDates(myDataTb);
		}

		/// <summary>
		/// The GetMagicUrls function is used to get all the MagicUrls in the module
		/// </summary>
		public DataTable GetMagicUrls(int PortalID) 
		{
			SqlDataAdapter myAdapt			= new SqlDataAdapter("dbo.adn_GetMagicUrls", myConnection);
            myAdapt.SelectCommand.CommandType = CommandType.StoredProcedure;

            AddPortalIDParameters(myAdapt.SelectCommand,PortalID);

            DataTable myDataTb					= new DataTable();
			try
			{
				myAdapt.Fill(myDataTb);
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]MagicUrlsDB GetMagicUrls  ex : {0}",exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}

            //  return the DataTable
			return CheckDataTableForDates(myDataTb);
		}


		/// <summary>
		/// The GetSingleMagicUrls function is used to Get a single FAQ
		///	from the database for display/edit
		/// </summary>
		public SqlDataReader GetSingleMagicUrls(int itemID) 
		{
			SqlCommand myCommand = new SqlCommand("dbo.adn_GetSingleMagicUrls", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            AddItemIDParameters(myCommand,itemID);

			SqlDataReader result			= null;
			try
			{
				myConnection.Open();
				result						= myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]MagicUrlsDB GetSingleMagicUrls itemID : {0} \nex : {1}",itemID,exp),DBTraceSwitch.Sw.Info);
				if ( result != null )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
			return result;
		}


		/// <summary>
		/// The DeleteMagicUrls function is used to remove MagicUrls from the DatabaseDB
		/// </summary>
		public void DeleteMagicUrls(int itemID)
		{
			SqlCommand myCommand = new SqlCommand("dbo.adn_DeleteMagicUrls", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            AddItemIDParameters(myCommand,itemID);

			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]MagicUrlsDB DeleteMagicUrls  itemID : {0} \nex : {1}",itemID, exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}


		/// <summary>
		/// The UpdateMagicUrls function is used to update changes to the MagicUrls
		/// </summary>
		public void UpdateMagicUrls(int itemID,string MagicKey,string MagicValue,string MagicComment,int MagicScheduleDay,int MagicScheduleHours) 
		{
			SqlCommand myCommand = new SqlCommand("dbo.adn_UpdateMagicUrls", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            AddItemIDParameters(myCommand,itemID);

            SqlParameter parameterMagicKey = new SqlParameter("@MagicKey", SqlDbType.NVarChar, 128);
            parameterMagicKey.Value = (MagicKey.Length > 128) ? MagicKey.Substring(0,128):MagicKey;
            myCommand.Parameters.Add(parameterMagicKey);

            SqlParameter parameterMagicValue = new SqlParameter("@MagicValue", SqlDbType.NVarChar, 512);
            parameterMagicValue.Value = (MagicValue.Length > 512) ? MagicValue.Substring(0,512):MagicValue;;
            myCommand.Parameters.Add(parameterMagicValue);
            
			SqlParameter parameterMagicComment	= new SqlParameter("@MagicComment", SqlDbType.NVarChar, 512);
            parameterMagicComment.Value			= (MagicComment.Length > 512) ? MagicComment.Substring(0,512):MagicComment;
            myCommand.Parameters.Add(parameterMagicComment);

			SqlParameter parameterMagicScheduleDay	= new SqlParameter("@MagicScheduleDay", SqlDbType.Int, 4);
            parameterMagicScheduleDay.Value			= MagicScheduleDay;
            myCommand.Parameters.Add(parameterMagicScheduleDay);

			SqlParameter parameterMagicScheduleHours	= new SqlParameter("@MagicScheduleHours", SqlDbType.Int, 4);
            parameterMagicScheduleHours.Value			= MagicScheduleHours;
            myCommand.Parameters.Add(parameterMagicScheduleHours);

			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]MagicUrlsDB UpdateMagicUrls  MagicKey:{0}, ex : {1}",MagicKey,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

		public DataTable GetAllMagicUrlsGlobalized() 
		{
			SqlDataAdapter myAdapt			    =   new SqlDataAdapter("dbo.adn_GetAllMagicUrlsGlobalized", myConnection);
			myAdapt.SelectCommand.CommandType   =   CommandType.StoredProcedure;

			AddGlobalizeParameters(myAdapt.SelectCommand);

			//  Create and Fill the DataSet
            DataTable myDataTb					=   new DataTable();
			try
			{
				myAdapt.Fill(myDataTb);
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]MagicUrlsDB GetMagicUrlsGLobalized  ex : {0}",exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}

			//  return the DataTable
			return CheckDataTableForDates(myDataTb);
		}

		public DataTable GetMagicUrlsGlobalized(int PortalID) 
		{
			SqlDataAdapter myAdapt			= new SqlDataAdapter("dbo.adn_GetMagicUrlsGlobalized", myConnection);
			myAdapt.SelectCommand.CommandType = CommandType.StoredProcedure;

			AddGlobalizeParameters(myAdapt.SelectCommand);

            AddPortalIDParameters(myAdapt.SelectCommand,PortalID);

            DataTable myDataTb					= new DataTable();
			try
			{
				myAdapt.Fill(myDataTb);
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]MagicUrlsDB GetMagicUrlsGLobalized  ex : {0}",exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}

			return CheckDataTableForDates(myDataTb);
		}

		public void UpdateMagicUrlsGlobalized(int itemID,string MagicKey,string MagicValue,string MagicComment,int MagicScheduleDay,int MagicScheduleHours) 
		{
			SqlCommand myCommand		        = new SqlCommand("dbo.adn_UpdateMagicUrlsGlobalized", myConnection);
			myCommand.CommandType		        = CommandType.StoredProcedure;

            AddItemIDParameters(myCommand,itemID);

			AddGlobalizeParameters(myCommand);

			SqlParameter parameterMagicKey		= new SqlParameter("@MagicKey", SqlDbType.NVarChar, 128);
			parameterMagicKey.Value				= (MagicKey.Length > 128) ? MagicKey.Substring(0,128):MagicKey;
			myCommand.Parameters.Add(parameterMagicKey);

			SqlParameter parameterMagicValue		= new SqlParameter("@MagicValue", SqlDbType.NVarChar, 512);
			parameterMagicValue.Value				= (MagicValue.Length > 512) ? MagicValue.Substring(0,512):MagicValue;
			myCommand.Parameters.Add(parameterMagicValue);
            
			SqlParameter parameterMagicComment	= new SqlParameter("@MagicComment", SqlDbType.NVarChar, 512);
            parameterMagicComment.Value			= (MagicComment.Length > 512) ? MagicComment.Substring(0,512):MagicComment;
            myCommand.Parameters.Add(parameterMagicComment);

			SqlParameter parameterMagicScheduleDay	= new SqlParameter("@MagicScheduleDay", SqlDbType.Int, 4);
            parameterMagicScheduleDay.Value			= MagicScheduleDay;
            myCommand.Parameters.Add(parameterMagicScheduleDay);

			SqlParameter parameterMagicScheduleHours	= new SqlParameter("@MagicScheduleHours", SqlDbType.Int, 4);
            parameterMagicScheduleHours.Value			= MagicScheduleHours;
            myCommand.Parameters.Add(parameterMagicScheduleHours);

			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]MagicUrlsDB UpdateMagicUrlsGlobalized  MagicKey:{0}, ex : {1}",MagicKey,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

		public SqlDataReader GetSingleMagicUrlsGlobalized(int itemID) 
		{
			SqlCommand myCommand = new SqlCommand("dbo.adn_GetSingleMagicUrlsGlobalized", myConnection);
			myCommand.CommandType = CommandType.StoredProcedure;

            AddItemIDParameters(myCommand,itemID);

			AddGlobalizeParameters(myCommand);

			SqlDataReader result			= null;
			try
			{
				myConnection.Open();
				result						= myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]MagicUrlsDB GetSingleMagicUrlsGlobalized itemID : {0} \nex : {1}",itemID,exp),DBTraceSwitch.Sw.Info);
				if ( result != null )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
			return result;
		}
		public void DeleteMagicUrlsGlobalized(int itemID)
		{
			SqlCommand myCommand = new SqlCommand("dbo.adn_DeleteMagicUrlsGlobalized", myConnection);
			myCommand.CommandType = CommandType.StoredProcedure;

            AddItemIDParameters(myCommand,itemID);

			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]MagicUrlsDB DeleteMagicUrlsGlobalized  itemID : {0} \nex : {1}",itemID, exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}
	}
}
